insert
    overwrite
    table jms_dm.dm_scan_bagging_true_summary_dt partition
    (dt)
select agent_code,
       agent_name,
       is_arrival_port,
       bag_scan_site_code,
       bag_scan_site,
       bag_scan_site_type,
       parent_network_code,
       parent_network_name,
       count(distinct if(package_code is not null, waybill_no, null))               as bag_cnt,
       sum(if(bag_err_type > 2 and dtype = 'error', 1, 0))                             bag_err_cnt,
--        count(distinct case when bag_err_type > 0 then if(package_code is not null, waybill_no, null) else 0 end)                               bag_err_cnt,
       if(count(distinct if(package_code is not null, waybill_no, null)) = 0, 0,
          round(sum(case when bag_err_type > 2 and dtype = 'error' then 1 else 0 end) /
                count(distinct if(package_code is not null, waybill_no, null)), 4)) as bag_err_rate,
       sum(if(bag_err_type = 1 and dtype = 'error', 1, 0))                             bag_sys_err_cnt,
       sum(if(bag_err_type = 2 and dtype = 'error', 1, 0))                             bill_sys_err_cnt,
       sum(if(bag_err_type = 3 and dtype = 'error', 1, 0))                             dev_err_cnt,
       sum(if(bag_err_type = 4 and dtype = 'error', 1, 0))                             tab_err_cnt,
       sum(if(is_return = 1 and dtype = 'error', 1, 0))                                return_cnt,
       sum(if(is_resend = 1 and dtype = 'error', 1, 0))                                resend_cnt,
       sum(if(bag_err_type = 1 and dtype = 'error', 1, 0))                             head_err_cnt,
       sum(if(is_bag_dup = 1 and dtype = 'error', 1, 0))                               dup_err_cnt,
       sum(if(is_trace_err = 1 and dtype = 'error', 1, 0))                             trace_err_cnt,
       date_time                                                                       statistic_date,
       sum(if(is_adjust = 1 and dtype = 'error', 1, 0))                                adjust_cnt,
       dt
from jms_dm.dm_scan_bagging_true_detail_dt
where dt = '{{ execution_date | cst_ds }}'
group by agent_code, bag_scan_site_type, agent_name, bag_scan_site_code, bag_scan_site, is_arrival_port, date_time, dt,
         parent_network_code,
         parent_network_name
    distribute by dt, PMOD(HASH(RAND()), 1);